import yahoo
import cx_Oracle
from stocktable import stocklist
import os

if __name__=="__main__":
    os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'
    connection = cx_Oracle.connect('istock_user', 'istock_user', 'localhost:1521/orcl')
    cursor = connection.cursor()    

    for i in range(0, len(stocklist)):
        stockid = stocklist[i][0]
        print stockid
        #if int(stockid) <= 600367:
        #    continue
        
        #shang hai
        if int(stockid) >= 600000:
            li=yahoo.get_historical_prices(stockid+'.ss', '19900101', '20110304')
        else:
            li=yahoo.get_historical_prices(stockid+'.sz', '19900101', '20110304')
        if li:
            print len(li)
            for j in range(1, len(li)):
                #date
                try:
                    datet = li[j][0][0:4]+li[j][0][5:7]+li[j][0][8:10]
                    openp = float(li[j][1])
                    highp = float(li[j][2])
                    lowp  = float(li[j][3])
                    closep = float(li[j][4])
                    volumn= int(li[j][5])
                    adjclosd = float(li[j][6])

                    sql = ("insert into STOCK_DAYLINE_INFO (id, stock_id, datetime, open, high, low, close, volumn, adjclose) values (SEQUENCE_DAYLINE_INFO.NEXTVAL,"+"'"+stockid+"'," + "'"+datet+"',"+"'"+str(openp)+"',"+"'"+str(highp)+"',"+"'"+str(lowp)+"',"+"'"+str(closep)+"'," + "'"+str(volumn)+"','"+str(adjclosd)+"')")
                    #print sql
                    cursor.execute(sql)
                except Exception, ex:
                    print "error" + stockid + str(ex)
            try:
                connection.commit()
            except:
                print "error"
        else:
            print 0
    cursor.close()
    connection.close()
